package com.rbs.dao.Impl;

import com.rbs.dao.BrandDao;
import com.rbs.entity.Brand;
import com.rbs.util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BrandDaoImpl implements BrandDao {
    @Override
    public Map<Integer, String> selectAllBrand() {
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        Map<Integer,String> map = new HashMap<>();
        try {
            conn = JDBCUtils.getConn();
            String sql = "select * from t_brand";
            psmt = conn.prepareStatement(sql);
            rs = psmt.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                map.put(id,name);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return map;
    }

    @Override
    public List<Brand> selectAllBrandAndPhoneNum() {
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        List<Brand> brandList = new ArrayList<>();
        try {
            conn = JDBCUtils.getConn();
            String sql = "select br.id,br.name,count(*) phoneNum\n" +
                    "from t_brand br left join t_phone pr\n" +
                    "on br.id = pr.brand_id\n" +
                    "group by br.id,br.name";
            psmt = conn.prepareStatement(sql);
            rs = psmt.executeQuery();
            while (rs.next()){
                Brand brand = new Brand();
                brand.setId(rs.getInt("id"));
                brand.setName(rs.getString("name"));
                brand.setPhoneNum(rs.getInt("phoneNum"));
                brandList.add(brand);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return brandList;
    }

    @Override
    public int addBrand(Brand brand) {
        Connection conn = null;
        PreparedStatement psmt = null;
        int i = 0;
        try {
            conn = JDBCUtils.getConn();
            String sql = "insert into t_brand values(null,?)";
            psmt = conn.prepareStatement(sql);
            //为占位符赋值
            psmt.setString(1,brand.getName());
            i = psmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public int deleteBrandById(int id) {
        Connection conn = null;
        PreparedStatement psmt = null;
        int i = 0;
        try {
            conn = JDBCUtils.getConn();
            //创建preparedStatement
            String sql = "delete from t_brand where id = ?";
            psmt = conn.prepareStatement(sql);
            //为占位符赋值
            psmt.setInt(1,id);
            //执行sql语句
            i = psmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
        return i;
    }

}
